using System;
using System.Reflection;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.OleDb;
using System.IO;
using log4net;

namespace DbSupportLib
{
    public static class Support
    {
        private static readonly ILog log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        public static string Version()
        {
            return "$Rev: 4 $";
        }


        /// <summary>
        /// Execute the SQL contained in the specified file
        /// </summary>
        /// <param name="conn">Open connection to the server</param>
        /// <param name="DbName">Name of the database the script should be executed in</param>
        /// <param name="FileName">Path to disk file containing SQL</param>
        public static void ExecuteScript(OleDbConnection conn, string DbName, string FileName)
        {
            if (!File.Exists(FileName))
            {
                log.FatalFormat("Specified PostScript file ({0}) does not exist", FileName);
                return;
            }
            System.IO.StreamReader sqlCmdFile = new System.IO.StreamReader(FileName);
            string sqlCmd = sqlCmdFile.ReadToEnd();
            sqlCmdFile.Close();

            log.InfoFormat("Execute Post Script:{0}", sqlCmd);

            OleDbCommand cmd = conn.CreateCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandTimeout = 3000;  // 3000 seconds = 50 minutes might be needed for the statistics

            string currentDatabase = conn.Database;

            cmd.CommandText = string.Format("use {0}", DbName);
            cmd.ExecuteNonQuery();

            cmd.CommandText = sqlCmd;
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception exc)
            {
                log.FatalFormat("Error during Post Script processing:{0}", exc.Message);
            }
            finally
            {
                if (!string.IsNullOrEmpty(currentDatabase))
                {
                    cmd.CommandText = string.Format("use {0}", currentDatabase);
                    cmd.ExecuteNonQuery();
                }
            }

            return;
        }

        public static bool LoadObject<T>(ref T dataObj, OleDbConnection conn, CommandType cmdType, string sqlCmd) where T : class, new()
        {
            try
            {
                // Get the user information
                OleDbCommand cmd = conn.CreateCommand();
                cmd.CommandType = cmdType;
                cmd.CommandText = sqlCmd;
                using (OleDbDataReader rdr = cmd.ExecuteReader())
                {
                    SetProperties(dataObj, rdr, BuildIdxTable(new T(), rdr));
                    return true;
                }
            }
            catch (Exception exc)
            {
                log.Fatal("Error during processing of LoadObject request:" + sqlCmd, exc);
            }
            return false;
        }

        public static bool LoadObject<T>(T dataObj, OleDbDataReader rdr) where T : class, new()
        {
            try
            {
                if (rdr.Read())
                {
                    SetProperties(dataObj, rdr, BuildIdxTable(new T(), rdr));
                    return true;
                }
            }
            catch (Exception exc)
            {
                log.Fatal("Error during processing of LoadObject");
            }
            return false;
        }


        public static List<T> QueryToList<T>(this List<T> objList, OleDbConnection conn, CommandType cmdType, string sqlCmd)
            where T : class, new()
        {
            try
            {
                // Get the user information
                OleDbCommand cmd = conn.CreateCommand();
                cmd.CommandType = cmdType;
                cmd.CommandText = sqlCmd;
                return QueryToList(objList, conn, cmd);
            }
            catch (Exception exc)
            {
                log.Fatal("Error during processing of QueryToList request:" + sqlCmd, exc);
            }
            return null;
        }

        public static List<T> QueryToList<T>(this List<T> objList, OleDbConnection conn, OleDbCommand cmd)
            where T : class, new()
        {
            try
            {
                using (OleDbDataReader rdr = cmd.ExecuteReader())
                {
                    try
                    {
                        List<T> result = Support.LoadList(objList, rdr);
                        log.InfoFormat("Returning {0} records from {1}", result.Count, cmd.CommandText);
                        return result;
                    }
                    catch (Exception exc)
                    {
                        log.Fatal("Error during processing of LoadList in QueryToList request:" + cmd.CommandText, exc);
                    }
                }
            }
            catch (Exception exc)
            {
                log.Fatal("Error during processing of QueryToList request:" + cmd.CommandText, exc);
            }
            return null;
        }

        public static Dictionary<string, int> BuildIdxTable(object obj, OleDbDataReader rdr)
        {
            Dictionary<string, int> indexTable = new Dictionary<string, int>();
            foreach (PropertyInfo prop in (obj.GetType()).GetProperties())
            {
                bool isMapped = true;

                // see if this is a property which should be mapped
                foreach (object propAttr in prop.GetCustomAttributes(true))
                {
                    if (propAttr is SqlInterfaceAttribute)
                    {
                        isMapped = ((SqlInterfaceAttribute)propAttr).IsMapped;
                        break;
                    }
                }

                int idx = -1;
                if (isMapped)
                {
                    try
                    {
                        idx = rdr.GetOrdinal(prop.Name);
                    }
                    catch (Exception)
                    {
                        idx = -1;
                        log.ErrorFormat("BuildIdxTable: Could not find a mapping for column {0}.{1}", (obj.GetType()).Name, prop.Name);
                    }
                }
                indexTable.Add(prop.Name, idx);
            }
            return indexTable;
        }

        public static object SetProperties(object obj, OleDbDataReader rdr, Dictionary<string, int> indexTable)
        {
            foreach (PropertyInfo prop in (obj.GetType()).GetProperties())
            {
                object[] attr = prop.GetCustomAttributes(false);
                if (indexTable[prop.Name] >= 0)
                {
                    if (prop.CanWrite && rdr[indexTable[prop.Name]] != DBNull.Value)
                    {
                        try
                        {
                            prop.SetValue(obj, rdr[indexTable[prop.Name]], null);
                        }
                        catch (Exception exc)
                        {
                            log.ErrorFormat("Error converting {0} : {1}", prop.Name, exc.Message);
                        }
                    }
                }
            }
            return obj;
        }


        public static List<T> LoadList<T>(this List<T> objList, OleDbDataReader rdr) where T : class, new()
        {
            System.Diagnostics.Debug.Assert(objList != null, "objList should not be null");
            try
            {
                Dictionary<string, int> indexTable = BuildIdxTable(new T(), rdr);
                while (rdr.Read())
                {
                    objList.Add( (T)SetProperties(new T(), rdr, indexTable));
                }
                return objList;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public static ObservableCollection<T> LoadList<T>(this ObservableCollection<T> objList, OleDbDataReader rdr) where T : class, new()
        {
            System.Diagnostics.Debug.Assert(objList != null, "objList should not be null");
            try
            {
                Dictionary<string, int> indexTable = BuildIdxTable(new T(), rdr);
                while (rdr.Read())
                {
                    objList.Add( (T)SetProperties(new T(), rdr, indexTable));
                }
                return objList;
            }
            catch (Exception)
            {
                throw;
            }
        }

    }
}
